﻿using System;
using PwdMgr.DataAccess;
using System.Data.Common;
using System.Data.SQLite;
using PwdMgr.CommonContract;
using System.Collections.Generic;
using System.Data;

namespace PwdMgr.DataAccess.SQLite
{
    public class SQLiteDataAccess : DBControl, IDataAccess
    {
        public SQLiteDataAccess(string connStr, bool autoCommit = true)
            : base(connStr, autoCommit)
        {

        }
        protected override DbConnection GetConnection(string connStr)
        {
            return new SQLiteConnection(connStr);
        }

        public void CreateUserInfo(UserInfo userInfo)
        {
            SQLiteParameter[] parameters = new SQLiteParameter[5];
            parameters[0] = new SQLiteParameter("@username", userInfo.UserName);
            parameters[1] = new SQLiteParameter("@password", userInfo.Password);
            parameters[2] = new SQLiteParameter("@email", userInfo.Email);
            parameters[3] = new SQLiteParameter("@question", userInfo.Question);
            parameters[4] = new SQLiteParameter("@answer", userInfo.Answer);

            ExecuteNonQuery("insert into tb_user(username, password, email, question, answer) values"
                + "(@username, @password, @email, @question, @answer)", parameters);
        }

        public int QueryUserInfoCount(string username)
        {
            SQLiteParameter param = new SQLiteParameter("@username", username);

            using (var reader = ExecuteReader(
                "select count(id) from tb_user where username = @username", param))
            {
                reader.Read();
                return (int)(long)reader[0];
            }
        }

        public int QueryUserInfoCount(string username, string password)
        {
            SQLiteParameter[] parameters = new SQLiteParameter[2];
            parameters[0] = new SQLiteParameter("@username", username);
            parameters[1] = new SQLiteParameter("@password", password);

            using (var reader = ExecuteReader(
                "select count(id) from tb_user where username = @username and password = @password",
                parameters))
            {
                reader.Read();
                return (int)(long)reader[0];
            }
        }

        public void CreateSensitiveInfo(SensitiveInfo info)
        {
            string cmdText = "insert into tb_sensitiveinfo(uid, name, pwd, tag1, tag2, tag3, tag4, tag5) "
                +"values(@uid, @name, @pwd, @tag1, @tag2, @tag3, @tag4, @tag5);";

            SQLiteParameter[] parameters = new SQLiteParameter[8];
            parameters[0] = new SQLiteParameter("@uid", info.UID);
            parameters[1] = new SQLiteParameter("@name", info.Username);
            parameters[2] = new SQLiteParameter("@pwd", info.Password);
            parameters[3] = new SQLiteParameter("@tag1", info.Tag1);
            parameters[4] = new SQLiteParameter("@tag2", info.Tag2);
            parameters[5] = new SQLiteParameter("@tag3", info.Tag3);
            parameters[6] = new SQLiteParameter("@tag4", info.Tag4);
            parameters[7] = new SQLiteParameter("@tag5", info.Tag5);

            ExecuteNonQuery(cmdText, parameters);
        }

        public UserInfo GetUser(string username, string password)
        {
            string cmdText = "select id, username, password, email from tb_user "
                +"where username = @username and password = @password";

            SQLiteParameter[] parameters = new SQLiteParameter[2];
            parameters[0] = new SQLiteParameter("@username", username);
            parameters[1] = new SQLiteParameter("@password", password);

            UserInfo userInfo = null;
            using (var reader = ExecuteReader(cmdText, parameters))
            {
                if (reader.Read())
                {
                    userInfo = new UserInfo();
                    userInfo.UserID = reader.GetInt32(0);
                    userInfo.UserName = reader.GetString(1);
                    userInfo.Password = reader.GetString(2);
                    userInfo.Email = reader.GetString(3);
                }
            }
            return userInfo;
        }

        public List<SensitiveInfo> GetSensitiveInfoByUID(int uid)
        {
            List<SensitiveInfo> list = new List<SensitiveInfo>();

            string cmdText = "select id, uid, name, pwd, tag1, tag2, tag3, tag4, tag5 from tb_sensitiveInfo"
                + " where uid = @uid";

            SQLiteParameter pa = new SQLiteParameter("@uid", uid);

            using (var reader = ExecuteReader(cmdText, pa))
            {
                while (reader.Read())
                {
                    SensitiveInfo info = Convert(reader);
                    list.Add(info);
                }
            }

            return list;
        }

        public List<SensitiveInfo> GetSensitiveInfo(string tag, int uid)
        {
            List<SensitiveInfo> list = new List<SensitiveInfo>();

            string cmdText = "select id, uid, name, pwd, tag1, tag2, tag3, tag4, tag5 from tb_sensitiveInfo"
                + " where uid = @uid and"
                + " (tag1 = @tag or tag2 = @tag or tag3 = @tag or tag4 = @tag or tag5 = @tag)";

            SQLiteParameter pUID = new SQLiteParameter("@uid", uid);
            SQLiteParameter pTag = new SQLiteParameter("@tag", tag);

            using (var reader = ExecuteReader(cmdText, pUID, pTag))
            {
                while (reader.Read())
                {
                    SensitiveInfo info = Convert(reader);
                    list.Add(info);
                }
            }

            return list;
        }

        private SensitiveInfo Convert(IDataRecord record)
        {
            if (record == null)
                return null;

            SensitiveInfo info = new SensitiveInfo();

            info.ID = record.GetInt32(0);
            info.UID = record.GetInt32(1);
            info.Username = record.GetString(2);
            info.Password = record.GetString(3);
            info.Tag1 = record.GetString(4);

            if (!record.IsDBNull(5))
                info.Tag2 = record.GetString(5);
            if (!record.IsDBNull(6))
                info.Tag3 = record.GetString(6);
            if (!record.IsDBNull(7))
                info.Tag4 = record.GetString(7);
            if (!record.IsDBNull(8))
                info.Tag5 = record.GetString(8);

            return info;
        }

        public int DeleteSensitiveInfo(int id)
        {
            string cmd = "delete from tb_sensitiveinfo where id = @id";

            SQLiteParameter parameter = new SQLiteParameter("@id", id);
            return ExecuteNonQuery(cmd, parameter);
        }

        protected override DbParameter NewParameter()
        {
            return new SQLiteParameter();
        }
    }
}
